{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Congestion Charging - Easy\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/f/f6/CongestionCharge.png)\n",
    "\n",
    "camera(**id**, perim)\n",
    "\n",
    "keeper(**id**, name, address)\n",
    "\n",
    "vehicle(**id**, keeper)\n",
    "\n",
    "image(**_camera_**, **whn**, reg)\n",
    "\n",
    "permit(**_reg_**, **sDate**, chargeType)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sample query\n",
    "\n",
    "List the vehicles for which 'Strenuous, Sam' is the registered keeper. The link between Keepers and Vehicles is via the foreign key specified in the CREATE TABLE vehicle statement. Note the line:\n",
    "\n",
    "```\n",
    " ,FOREIGN KEY(keeper) REFERENCES keeper(id)\n",
    "```\n",
    "\n",
    "This will be the basis of our join condition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "Show the name and address of the keeper of vehicle SO 02 PSP."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Show the number of cameras that take images for incoming vehicles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "List the image details taken by Camera 10 before 26 Feb 2007."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "List the number of images taken by each camera. Your answer should show how many images have been taken by camera 1, camera 2 etc. The list must NOT include the images taken by camera 15, 16, 17, 18 and 19."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "A number of vehicles have permits that start on 30th Jan 2007. List the name and address for each keeper in alphabetical order without duplication."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
